package com.un.ebs.inventory.mapper;

import com.baomidou.mybatisplus.annotation.SqlParser;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.un.ebs.inventory.domain.InventoryNote;
import com.un.ebs.inventory.domain.InventoryNoteExt;
import com.un.ebs.inventory.domain.InventoryNoteItem;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.security.core.parameters.P;
import org.springframework.stereotype.Component;
import org.springframework.test.context.jdbc.Sql;

import java.util.List;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author liubo
 * @since 2020-05-23
 */
@Component
public interface InventoryNoteMapper extends BaseMapper<InventoryNote> {

    @Select("SELECT ISNULL(MAX(RIGHT(code,4)),0) FROM dbo.tb_inventory_note WHERE CONVERT(VARCHAR(6),create_time,112)= CONVERT(VARCHAR(6),GETDATE(),112)")
    int maxCode();

    @Update("update tb_inventory_note_item set price = #{price} where id = #{id}")
    void updateCostPrice(@Param("id") String id, @Param("price") double price);

    /**
     * 查询可销售商品库存，仓库属性必须为成品库
     *
     * @param skuId
     * @param warehouseId
     * @return
     */
    @Select("SELECT ISNULL(qty,0) AS qty FROM v_inventory_ext WHERE is_product=1 AND warehouse_id=#{warehouseId} AND sku_id=#{skuId}")
    Double selectCanSaleInventory(@Param("skuId") String skuId, @Param("warehouseId") String warehouseId);

    @Select("SELECT ISNULL(qty,0) AS qty FROM tb_inventory WHERE warehouse_id=#{warehouseId} AND sku_id=#{skuId}")
    Double selectInventoryQty(@Param("skuId") String skuId, @Param("warehouseId") String warehouseId);

    @SqlParser(filter = true)
    @Select("SELECT ROUND(isnull(SUM(cost_money),0),2) AS cost_money FROM dbo.v_inventory_note_item_ext WHERE note_type = #{inventoryType} AND account_month=#{month} and account_year=#{year} AND is_delete=0 AND submit_status=1 AND audit_status=1 and tenant_id = #{tenantId} GROUP BY tenant_id")
    Double selectCostMoney(@Param("inventoryType") int inventoryType, @Param("year") int year, @Param("month") int month, @Param("tenantId") String tenantId);

    @Select("select * from tb_inventory_note_item where note_id=#{id}")
    List<InventoryNoteItem> selectItemByNoteId(@Param("id") String id);

    @SqlParser(filter = true)
    @Select("select * from tb_inventory_note where id=#{id}")
    InventoryNote queryDeleteId(@Param("id") String id);

    @Select("select * from v_inventory_note_ext where relation_note_id = #{relationId} and is_delete = 0")
    List<InventoryNoteExt> selectByRelationId(@Param("relationId") String relationId);

    @Select("select id from tb_inventory_note where relation_note_id = #{relationId} and is_delete=0 and is_rebuild=1")
    String selectIdByRelationIdAndRebuild(@Param("relationId") String relationId);

    @SqlParser(filter = true)
    @Update("update tb_inventory_note set is_delete=0 where id=#{id}")
    void recovery(@Param("id") String id);

    @Select("select isnull(sum(b.qty),0) from tb_production_order a,tb_production_order_item b where a.id=b.order_id and a.id=#{noteId} and  a.submit_status=1 and a.is_delete=0")
    Double sumProductionPlanQty(@Param("noteId")String noteId);

    @Select("select isnull(sum(qty),0) from tb_inventory_note a,tb_inventory_note_item b,tb_inventory_note_type c where a.id=b.note_id and a.note_type_id=c.id and a.submit_status=1 and a.audit_status=1 and a.relation_note_id=#{noteId} and c.inventory_type=40")
    Double sumProductionInQty(@Param("noteId") String noteId);

//    @Select("SELECT COUNT(*) FROM dbo.tb_inventory_note_item WHERE note_id=#{id} AND check_note_id IS NULL")
//    int selectNotCheckItemCount(@Param("id") String id);

}
